Skip Headers

Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# ALTER DATABASE

Purpose

Use the `ALTER` `DATABASE` statement to modify, maintain, or recover an existing database.

Note: In earlier versions of Oracle Database, you could use the `ALTER` `DATABASE` for two conversion operations: The `RESET` `COMPATIBILITY` clause lets you reset the database to an earlier version at the next instance startup. The `CONVERT` clause lets you upgrade an Oracle7 data dictionary to an Oracle8i or Oracle9i data dictionary. These clauses are no longer supported. Please refer to Oracle Database Upgrade Guide for more information on migration and interoperability issues.

See Also: Oracle Database Backup and Recovery Basics for examples of performing media recovery Oracle Data Guard Concepts and Administration for additional information on using the `ALTER` `DATABASE` statement to maintain standby databases CREATE DATABASE for information on creating a database

Prerequisites

You must have the `ALTER` `DATABASE` system privilege.

To specify the `RECOVER` clause, you must also have the `SYSDBA` system privilege.

Syntax

alter_database::=

Description of the illustration alter_database.gif

Groups of ALTER DATABASE syntax:

- startup_clauses::=

- recovery_clauses ::=

- database_file_clauses ::=

- logfile_clauses ::=

- controlfile_clauses ::=

- standby_database_clauses ::=

- default_settings_clauses::=

- instance_clauses::=

- security_clause ::=

startup_clauses::=

Description of the illustration startup_clauses.gif

recovery_clauses ::=

Description of the illustration recovery_clauses.gif

(general_recovery ::=, managed_standby_recovery::=)

general_recovery ::=

Description of the illustration general_recovery.gif

(full_database_recovery ::=, partial_database_recovery::=)

full_database_recovery ::=

Description of the illustration full_database_recovery.gif

partial_database_recovery::=

Description of the illustration partial_database_recovery.gif

parallel_clause::=

Description of the illustration parallel_clause.gif

managed_standby_recovery::=

Description of the illustration managed_standby_recovery.gif

(redo_apply_clauses::=, cancel_clause::=, finish_clause::=)

redo_apply_clauses::=

Description of the illustration redo_apply_clauses.gif

Note: Several subclauses of the `redo_apply_clauses` are no longer needed and have been deprecated. These clauses no longer appear in the preceding diagram. Please refer to the semantics of redo_apply_clauses.

finish_clause::=

Description of the illustration finish_clause.gif

cancel_clause::=

Description of the illustration cancel_clause.gif

database_file_clauses ::=

Description of the illustration database_file_clauses.gif

(create_datafile_clause::=, alter_datafile_clause::=, alter_tempfile_clause::=)

create_datafile_clause::=

Description of the illustration create_datafile_clause.gif

(file_specification::=)

alter_datafile_clause::=

Description of the illustration alter_datafile_clause.gif

(autoextend_clause ::=, size_clause::=)

alter_tempfile_clause::=

Description of the illustration alter_tempfile_clause.gif

(autoextend_clause ::=, size_clause::=)

autoextend_clause ::=

Description of the illustration autoextend_clause.gif

maxsize_clause::=

Description of the illustration maxsize_clause.gif

(size_clause::=)

logfile_clauses ::=

Description of the illustration logfile_clauses.gif

(logfile_descriptor::=, add_logfile_clauses::=, drop_logfile_clauses::=, supplemental_db_logging ::=)

add_logfile_clauses::=

Description of the illustration add_logfile_clauses.gif

(redo_log_file_spec::=, logfile_descriptor::=)

drop_logfile_clauses::=

Description of the illustration drop_logfile_clauses.gif

(logfile_descriptor::=)

supplemental_db_logging ::=

Description of the illustration supplemental_db_logging.gif

(supplemental_id_key_clause::=)

supplemental_id_key_clause::=

Description of the illustration supplemental_id_key_clause.gif

logfile_descriptor::=

Description of the illustration logfile_descriptor.gif

controlfile_clauses ::=

Description of the illustration controlfile_clauses.gif

(trace_file_clause::=)

trace_file_clause::=

Description of the illustration trace_file_clause.gif

standby_database_clauses ::=

Description of the illustration standby_database_clauses.gif

(activate_standby_db_clause::=, maximize_standby_db_clause::=, register_logfile_clause::=, commit_switchover_clause::=, start_standby_clause::=, stop_standby_clause::=, convert_standby_clause::=, parallel_clause::=)

activate_standby_db_clause::=

Description of the illustration activate_standby_db_clause.gif

maximize_standby_db_clause::=

Description of the illustration maximize_standby_db_clause.gif

register_logfile_clause::=

Description of the illustration register_logfile_clause.gif

(file_specification::=)

commit_switchover_clause::=

Description of the illustration commit_switchover_clause.gif

start_standby_clause::=

Description of the illustration start_standby_clause.gif

stop_standby_clause::=

Description of the illustration stop_standby_clause.gif

convert_standby_clause::=

Description of the illustration convert_standby_clause.gif

default_settings_clauses::=

Description of the illustration default_settings_clauses.gif

(flashback_mode_clause ::=, set_time_zone_clause::=)

set_time_zone_clause::=

Description of the illustration set_time_zone_clause.gif

flashback_mode_clause ::=

Description of the illustration flashback_mode_clause.gif

instance_clauses::=

Description of the illustration instance_clauses.gif

security_clause ::=

Description of the illustration security_clause.gif

Semantics

database

Specify the name of the database to be altered. The database name can contain only ASCII characters. If you omit `database`, Oracle Database alters the database identified by the value of the initialization parameter `DB_NAME`. You can alter only the database whose control files are specified by the initialization parameter `CONTROL_FILES`. The database identifier is not related to the Oracle Net database specification.

startup_clauses

The `startup_clauses` let you mount and open the database so that it is accessible to users.

MOUNT Clause

Use the `MOUNT` clause to mount the database. Do not use this clause when the database is already mounted.

MOUNT STANDBY DATABASE You can specify `MOUNT` `STANDBY` `DATABASE` to mount a physical standby database. The keywords `STANDBY` `DATABASE` are optional, because Oracle Database determines automatically whether the database to be mounted is a primary or standby database. As soon as this statement executes, the standby instance can receive archived redo logs from the primary instance and can archive the logs to the `STANDBY_ARCHIVE_DEST` location.

See Also: Oracle Data Guard Concepts and Administration for more information on standby databases

MOUNT CLONE DATABASE Specify `MOUNT` `CLONE` `DATABASE` to mount the clone database.

OPEN Clause

Use the `OPEN` clause to make the database available for normal use. You must mount the database before you can open it.

If you specify only `OPEN`, without any other keywords, the default is `OPEN` `READ` `WRITE` `NORESETLOGS` on a primary database and `OPEN` `READ` `ONLY` on a standby database.

OPEN READ WRITE Specify `OPEN` `READ` `WRITE` to open the database in read/write mode, allowing users to generate redo logs. This is the default if you are opening a primary database. You cannot specify this clause for a physical standby database.

See Also: "READ ONLY / READ WRITE: Example"

RESETLOGS | NORESETLOGS This clause determines whether Oracle Database resets the current log sequence number to 1, archives any unarchived logs (including the current log), and discards any redo information that was not applied during recovery, ensuring that it will never be applied. Oracle Database uses `NORESETLOGS` automatically except in the following specific situations, which require a setting for this clause:

- You must specify `RESETLOGS`:

  - After performing incomplete media recovery or media recovery using a backup controlfile

  - After a previous `OPEN` `RESETLOGS` operation that did not complete

  - After a `FLASHBACK` `DATABASE` operation

- If a created controlfile is mounted, then you must specify `RESETLOGS` if the online logs are lost, or you must specify `NORESETLOGS` if they are not lost.

UPGRADE | DOWNGRADE Use these `OPEN` clause parameters only if you are upgrading or downgrading a database. This clause instructs Oracle Database to modify system parameters dynamically as required for upgrade and downgrade, respectively. You can achieve the same result using the SQL*Plus `STARTUP` `UPGRADE` or `STARTUP` `DOWNGRADE` command.

See Also: Oracle Database Upgrade Guide for information on the steps required to upgrade or downgrade a database from one release to another SQL*Plus User's Guide and Reference for information on the SQL*Plus `STARTUP` command

OPEN READ ONLY Specify `OPEN` `READ` `ONLY` to restrict users to read-only transactions, preventing them from generating redo logs. This setting is the default when you are opening a physical standby database, so that the physical standby database is available for queries even while archive logs are being copied from the primary database site.

Restrictions on Opening a Database The following restrictions apply to opening a database:

- You cannot open a database in `READ` `ONLY` mode if it is currently opened in `READ` `WRITE` mode by another instance.

- You cannot open a database in `READ` `ONLY` mode if it requires recovery.

- You cannot take tablespaces offline while the database is open in `READ` `ONLY` mode. However, you can take datafiles offline and online, and you can recover offline datafiles and tablespaces while the database is open in `READ` `ONLY` mode.

recovery_clauses

The `recovery_clauses` include post-backup operations. For all of these clauses, Oracle Database recovers the database using any incarnations of datafiles and log files that are known to the current control file.

See Also: Oracle Database Backup and Recovery Basics for information on backing up the database and "Database Recovery: Examples"

ge neral_recove ry

The `general_recovery` clause lets you control media recovery for the database or standby database or for specified tablespaces or files. You can use this clause when your instance has the database mounted, open or closed, and the files involved are not in use.

Note: Parallelism is enabled by default during full or partial database recovery and logfile recovery. You can disable parallelism of these operations by specifying `NOPARALLEL` as shown in the respective syntax diagrams.

Restrictions on General Database Recovery General recovery is subject to the following restrictions:

- You can recover the entire database only when the database is closed.

- Your instance must have the database mounted in exclusive mode.

- You can recover tablespaces or datafiles when the database is open or closed, if the tablespaces or datafiles to be recovered are offline.

- You cannot perform media recovery if you are connected to Oracle Database through the shared server architecture.

Note: If you do not have special media requirements, Oracle recommends that you use the SQL*Plus `RECOVER` command rather than the `general_recovery_clause`.

See Also: Oracle Database Backup and Recovery Basics for more information on RMAN media recovery and user-defined media recovery SQL*Plus User's Guide and Reference for information on the SQL*Plus `RECOVER` command

AUTOMATIC

Specify `AUTOMATIC` if you want Oracle Database to automatically generate the name of the next archived redo log file needed to continue the recovery operation. If the `LOG_ARCHIVE_DEST_``n` parameters are defined, Oracle Database scans those that are valid and enabled for the first local destination. It uses that destination in conjunction with `LOG_ARCHIVE_FORMAT` to generate the target redo log filename. If the `LOG_ARCHIVE_DEST_``n` parameters are not defined, Oracle Database uses the value of the `LOG_ARCHIVE_DEST` parameter instead.

If the resulting file is found, Oracle Database applies the redo contained in that file. If the file is not found, Oracle Database prompts you for a filename, displaying the generated filename as a suggestion.

If you specify neither `AUTOMATIC` nor `LOGFILE`, Oracle Database prompts you for a filename, displaying the generated filename as a suggestion. You can then accept the generated filename or replace it with a fully qualified filename. If you know that the archived filename differs from what Oracle Database would generate, you can save time by using the `LOGFILE` clause.

FROM 'location'

Specify `FROM` `'location'` to indicate the location from which the archived redo log file group is read. The value of `location` must be a fully specified file location following the conventions of your operating system. If you omit this parameter, Oracle Database assumes that the archived redo log file group is in the location specified by the initialization parameter `LOG_ARCHIVE_DEST` or `LOG_ARCHIVE_DEST_1`.

full_database_recovery

The `full_database_recovery` clause lets you recover an entire database.

DATABASE Specify the `DATABASE` clause to recover the entire database. This is the default. You can use this clause only when the database is closed.

STANDBY DATABASE Specify the `STANDBY` `DATABASE` clause to manually recover a physical standby database using the control file and archived redo log files copied from the primary database. The standby database must be mounted but not open.

This clause recovers only online datafiles.

- Use the `UNTIL` clause to specify the duration of the recovery operation.

  - `CANCEL` indicates cancel-based recovery. This clause recovers the database until you issue the `ALTER` `DATABASE` statement with the `RECOVER` `CANCEL` clause.

  - `TIME` indicates time-based recovery. This parameter recovers the database to the time specified by the date. The date must be a character literal in the format `'YYYY-MM-DD:HH24:MI:SS'`.

  - `CHANGE` indicates change-based recovery. This parameter recovers the database to a transaction-consistent state immediately before the system change number specified by `integer`.

- Specify `USING` `BACKUP` `CONTROLFILE` if you want to use a backup control file instead of the current control file.

partial_database_recovery

The `partial_database_recovery` clause lets you recover individual tablespaces and datafiles.

TABLESPACE Specify the `TABLESPACE` clause to recover only the specified tablespaces. You can use this clause if the database is open or closed, provided the tablespaces to be recovered are offline.

See Also: "Using Parallel Recovery Processes: Example"

DATAFILE Specify the `DATAFILE` clause to recover the specified datafiles. You can use this clause when the database is open or closed, provided the datafiles to be recovered are offline.

You can identify the datafile by name or by number. If you identify it by number, then `filenumber` is an integer representing the number found in the `FILE#` column of the `V$DATAFILE` dynamic performance view or in the `FILE_ID` column of the `DBA_DATA_FILES` data dictionary view.

STANDBY TABLESPACE Specify `STANDBY` `TABLESPACE` to reconstruct a lost or damaged tablespace in the standby database using archived redo log files copied from the primary database and a control file.

STANDBY DATAFILE Specify `STANDBY` `DATAFILE` to manually reconstruct a lost or damaged datafile in the physical standby database using archived redo log files copied from the primary database and a control file. You can identify the file by name or by number, as described for the DATAFILE clause.

Specify `UNTIL` [`CONSISTENT` `WITH`] `CONTROLFILE` if you want the recovery of an old standby datafile or tablespace to use the current standby database control file. However, any redo in advance of the standby controlfile will not be applied. The keywords `CONSISTENT` `WITH` are optional and are provided for semantic clarity.

LOGFILE

Specify the `LOGFILE` '`filename`' to continue media recovery by applying the specified redo log file.

TEST

Use the `TEST` clause to conduct a trial recovery. A trial recovery is useful if a normal recovery procedure has encountered some problem. It lets you look ahead into the redo stream to detect possible additional problems. The trial recovery applies redo in a way similar to normal recovery, but it does not write changes to disk, and it rolls back its changes at the end of the trial recovery.

You can use this clause only if you have restored a backup taken since the last `RESETLOGS` operation. Otherwise, Oracle Database returns an error.

ALLOW ... CORRUPTION

The `ALLOW` `integer` `CORRUPTION` clause lets you specify, in the event of logfile corruption, the number of corrupt blocks that can be tolerated while allowing recovery to proceed.

When you use this clause during trial recovery (that is, in conjunction with the `TEST` clause), `integer` can exceed 1. When using this clause during normal recovery, `integer` cannot exceed 1.

See Also: Oracle Database Backup and Recovery Basics for information on database recovery in general Oracle Data Guard Concepts and Administration for information on managed recovery of standby databases

CONTINUE

Specify `CONTINUE` to continue multi-instance recovery after it has been interrupted to disable a thread.

Specify `CONTINUE` `DEFAULT` to continue recovery using the redo log file that Oracle Database would automatically generate if no other logfile were specified. This clause is equivalent to specifying `AUTOMATIC`, except that Oracle Database does not prompt for a filename.

CANCEL

Specify `CANCEL` to terminate cancel-based recovery.

managed_standby_recovery

Use the `managed_standby_recovery` clause to start and stop Redo Apply on a physical standby database. Redo Apply keeps the standby database transactionally consistent with the primary database by continuously applying redo received from the primary database.

A primary database transmits its redo data to standby sites. As the redo data is written to redo log files at the physical standby site, the log files become available for use by Redo Apply. You can use the `managed_standby_recovery` clause when your instance has the database mounted or is opened read-only. In the latter case, the database is implicitly closed before Redo Apply is started.

Restrictions on Managed Standby Recovery The same restrictions listed under general_recovery apply to this clause.

See Also: Oracle Data Guard Concepts and Administration for more information on the parameters of this clause "Recovering a Managed Standby Database: Examples"

redo_apply_clauses

Use these clauses to specify the details of redo apply.

Note: Parallelism is enabled by default during redo apply. You can disable parallelism of these operations by specifying `NOPARALLEL` as shown in the respective syntax diagrams.

USING CURRENT LOGFILE Clause Specify `USING` `CURRENT` `LOGFILE` to invoke real-time apply, which recovers redo from the standby redo log files as soon as they are written, without requiring them to be archived first at the physical standby database.

See Also: Oracle Data Guard Concepts and Administration for more information on real-time apply

DISCONNECT Specify `DISCONNECT` to indicate that Redo Apply should be performed in the background. Doing so leaves the current session available for other tasks. The `FROM` `SESSION` keywords are optional and are provided for semantic clarity.

NODELAY The `NODELAY` clause overrides the `DELAY` attribute on the `LOG_ARCHIVE_DEST_n` parameter on the primary database. If you do not specify the `NODELAY` clause, application of the archived redo log file is delayed according to the `DELAY` attribute of the `LOG_ARCHIVE_DEST_n` setting (if any). If the `DELAY` attribute was not specified on that parameter, the archived redo log file is applied immediately to the standby database.

UNTIL CHANGE Clause Use this clause to instruct Redo Apply to recover redo data up to, but not including, the specified system change number.

cancel_clause Specify `CANCEL` to stop Redo Apply after completely applying the current redo log file. If you specify only the `CANCEL` keyword, session control returns when Redo Apply actually stops.

- Include the `IMMEDIATE` keyword to stop Redo Apply before completely applying the current redo log file. Session control returns when Redo Apply actually stops.

- Include the `NOWAIT` keyword to return session control without waiting for the `CANCEL` operation to complete.

finish_clause Specify `FINISH` to complete applying all available redo data in preparation for a failover. The `FINISH` clause can include the following optional keywords:

- `FORCE` terminates inactive redo transport sessions that would otherwise prevent `FINISH` processing from beginning.

- `NOWAIT` returns control to the foreground process before the recovery completes

- `WAIT` (the default) returns control to the foreground process after recovery completes

Use the `FINISH` clause only in the event of the failure of the primary database. This clause overrides any specified delay intervals and applies all available redo immediately. After the `FINISH` command completes, this database can no longer run in the standby database role, and it must be converted to a primary database by issuing the `ALTER` `DATABASE` `COMMIT` `TO` `SWITCHOVER` `TO` `PRIMARY` statement.

TO LOGICAL STANDBY Clause Use this clause to convert a physical standby database into a logical standby database. You must specify a database name to identify the new logical standby database. If you are using a server parameter file (spfile) at the time you issue this statement, then the database will update the file with appropriate information about the new logical standby database. If you are not using an spfile, then the database issues a message reminding you to set the name of the `DB_NAME` parameter after shutting down the database. In addition, you must invoke the `DBMS_LOGSTDBY.BUILD` PL/SQL procedure on the primary database before using this clause on the standby database.

See Also: PL/SQL Packages and Types Reference for information about the `DBMS_LOGSTDBY.BUILD` procedure

Deprecated Redo Apply Clauses

The following clauses appeared in the syntax of earlier releases. They have been deprecated. Oracle recommends that you do not use these clauses. Alternatives are recommended in Table 10-1 and in the descriptions of the deprecated clauses that follow the table.

Table 10-1 Deprecated Redo Apply Clauses

Deprecated Syntax | Recommended Alternative
--- | ---
`DEFAULT` `DELAY` | `DELAY` `integer` | Use the `DELAY` attribute on the `LOG_ARCHIVE_DEST_n` initialization parameter
`NEXT` `integer` | Use the `UNTIL` `CHANGE` clause
`EXPIRE` `integer` | `NO` `EXPIRE` | Use the `CANCEL` clause to stop Redo Apply
`TIMEOUT` `integer` | `NO` `TIMEOUT` | Use the `CANCEL` clause to stop Redo Apply

DEFAULT DELAY | DELAY integer The `DELAY` `integer` and `DEFAULT` `DELAY` clauses are deprecated. Oracle recommends you use the `DELAY` attribute of the `LOG_ARCHIVE_DEST_n` initialization parameter instead. See Oracle Data Guard Concepts and Administration for information about that parameter. The behavior of these clauses is presented here for backward compatibility:

- `DELAY` creates a time lag between the time when redo data is received from the primary database and when it is applied to the standby database. When you set a `DELAY` interval, it does not delay the transport of the redo data to the standby database. Instead, the time lag you specify begins when the redo data is completely archived at the standby destination. You cannot specify both `NODELAY` and `DELAY`.

- `DEFAULT` `DELAY` reverts to the number of minutes specified by the `DELAY` attribute on the `LOG_ARCHIVE_DEST_n` initialization parameter on the primary database.

NEXT integer The `NEXT` clause is deprecated. Oracle recommends you use the `UNTIL` `CHANGE` `integer` syntax instead. Please see "UNTIL CHANGE Clause". The behavior of this clause is presented here for backward compatibility:

Use the `NEXT` clause to apply the specified number of archived redo log files as soon as possible after they have been archived. This parameter temporarily overrides any `DELAY` attribute specified on the `LOG_ARCHIVE_DEST_n` initialization parameter on the primary database and any `DELAY` values specified in an earlier `managed_standby_recovery` clause on the `ALTER` `DATABASE` statement. Once the number of archived redo log files specified by integer are processed, any previously specified delay takes effect again.

EXPIRE integer | NOEXPIRE The `EXPIRE` and `NOEXPIRE` clauses are deprecated. Oracle recommends that you use the `CANCEL` clause instead of `EXPIRE` to stop Redo Apply. Please see cancel_clause. The behavior of these clauses is presented here for backward compatibility.

- `EXPIRE` indicates the number of minutes from the current time after which Redo Apply will automatically stop. Redo Apply may expire after the actual interval specified, because Oracle will finish processing the archived redo log file that is being applied at the expiration time.

- `NOEXPIRE` disables a previously specified `EXPIRE` clause.

Expiration is always relative to the time the current statement is issued rather than to when Redo Apply was started. To terminate existing Redo Apply processing, use the `CANCEL` clause.

TIMEOUT integer | NOTIMEOUT The `TIMEOUT` and `NOTIMEOUT` clauses are deprecated. Oracle recommends you use the `CANCEL` clause instead of `TIMEOUT` to stop Redo Apply. Please see cancel_clause. The behavior of these clauses is presented here for backward compatibility:

- `TIMEOUT` indicates the number of minutes Redo Apply waits for the next archived redo log file to arrive from the primary database. If another log file does not arrive within the specified time, Redo Apply automatically stops and returns an error message. You can then issue the statement again to restart Redo Apply.

- If you omit `TIMEOUT` or if you specify `NOTIMEOUT`, then Redo Apply remains active until you reissue the statement with the `CANCEL` clause

You cannot specify both `TIMEOUT` and `DISCONNECT` [`FROM` `SESSION`]. `TIMEOUT` applies only to foreground recovery operations, whereas the `DISCONNECT` clause initiates background recovery operations.

BACKUP Clauses

Use these clauses to move all the datafiles in the database into or out of online backup mode (also called hot backup mode).

See Also: ALTER TABLESPACE for information on moving all datafiles in an individual tablespace into and out of online backup mode

BEGIN BACKUP Clause

Specify `BEGIN` `BACKUP` to move all datafiles in the database into online backup mode. The database must be mounted and open, and media recovery must be enabled (the database must be in archivelog mode).

While the database is in online backup mode, you cannot shut down the instance normally, begin backup of an individual tablespace, or take any tablespace offline or make it read only.

This clause has no effect on datafiles that are in offline or on read-only tablespaces.

END BACKUP Clause

Specify `END` `BACKUP` to take out of online backup mode any datafiles in the database currently in online backup mode. The database must be mounted (either open or closed) when you perform this operation.

After a system failure, instance failure, or `SHUTDOWN` `ABORT` operation, Oracle Database does not know whether the files in online backup mode match the files at the time the system crashed. If you know the files are consistent, you can take either individual datafiles or all datafiles out of online backup mode. Doing so avoids media recovery of the files upon startup.

- To take an individual datafile out of online backup mode, use the `ALTER` `DATABASE` `DATAFILE` ... `END` `BACKUP` statement. See database_file_clauses .

- To take all datafiles in a tablespace out of online backup mode, use an `ALTER` `TABLESPACE` ... `END` `BACKUP` statement.

database_file_clauses

The `database_file_clauses` let you modify datafiles and tempfiles. You can use any of the following clauses when your instance has the database mounted, open or closed, and the files involved are not in use.

RENAME FILE Clause

Use the `RENAME` `FILE` clause to rename datafiles, tempfiles, or redo log file members. You must create each filename using the conventions for filenames on your operating system before specifying this clause.

- To use this clause for a datafile or tempfile, the database must be mounted. The database can also be open, but the datafile or tempfile being renamed must be offline. In addition, a file with the new name must exist on the system.

- To use this clause for logfiles, the database must be mounted but not open.

- If you have enabled block change tracking, you can use this clause to rename the block change tracking file. The database must be mounted but not open when you rename the block change tracking file.

This clause renames only files in the control file. It does not actually rename them on your operating system. The operating system files continue to exist, but Oracle Database no longer uses them.

See Also: Oracle Database Backup and Recovery Basics for information on recovery of datafiles and tempfiles "Renaming a Log File Member: Example" and "Manipulating Tempfiles: Example"

create_datafile_clause

Use the `CREATE` `DATAFILE` clause to create a new empty datafile in place of an old one. You can use this clause to re-create a datafile that was lost with no backup. The `filename` or `filenumber` must identify a file that is or was once part of the database. If you identify the file by number, then `filenumber` is an integer representing the number found in the `FILE#` column of the `V$DATAFILE` dynamic performance view or in the `FILE_ID` column of the `DBA_DATA_FILES` data dictionary view.

- Specify `AS` `NEW` to create an Oracle-managed datafile with a system-generated filename, the same size as the file being replaced, in the default file system location for datafiles.

- Specify `AS` `file_specification` to assign a file name (and optional size) to the new datafile. Use the `datafile_tempfile_spec` form of `file_specification` (see file_specification) to list regular datafiles and tempfiles in an operating system file system or to list Automatic Storage Management disk group files.

If the original file (`filename` or `filenumber`) is an existing Oracle-managed datafile, then Oracle Database attempts to delete the original file after creating the new file. If the original file is an existing user-managed datafile, Oracle Database does not attempt to delete the original file.

If you omit the `AS` clause entirely, Oracle Database creates the new file with the same name and size as the file specified by `filename` or `filenumber`.

During recovery, all archived redo logs written to since the original datafile was created must be applied to the new, empty version of the lost datafile.

Oracle Database creates the new file in the same state as the old file when it was created. You must perform media recovery on the new file to return it to the state of the old file at the time it was lost.

Restrictions on Creating New Datafiles The creation of new datafiles is subject to the following restrictions:

- You cannot create a new file based on the first datafile of the `SYSTEM` tablespace.

- You cannot specify the `autoextend_clause` of `datafile_tempfile_spec` in this `CREATE` `DATAFILE` clause.

See Also: "DATAFILE Clause" of `CREATE` `DATABASE` for information on the result of this clause if you do not specify a name for the new datafile file_specification for a full description of the file specification (`datafile_tempfile_spec`) and "Creating a New Datafile: Example"

alter_datafile_clause

The `DATAFILE` clause lets you manipulate a file that you identify by name or by number. If you identify it by number, then `filenumber` is an integer representing the number found in the `FILE#` column of the `V$DATAFILE` dynamic performance view or in the `FILE_ID` column of the `DBA_DATA_FILES` data dictionary view. The `DATAFILE` clauses affect your database files as follows:

ONLINE Specify `ONLINE` to bring the datafile online.

OFFLINE Specify `OFFLINE` to take the datafile offline. If the database is open, you must perform media recovery on the datafile before bringing it back online, because a checkpoint is not performed on the datafile before it is taken offline.

FOR DROP If the database is in noarchivelog mode, you must specify `FOR` `DROP` clause to take a datafile offline. However, this clause does not remove the datafile from the database. To do that, you must use an operating system command or drop the tablespace in which the datafile resides. Until you do so, the datafile remains in the data dictionary with the status `RECOVER` or `OFFLINE`.

If the database is in archivelog mode, Oracle Database ignores the `FOR` `DROP` clause.

RESIZE Specify `RESIZE` if you want Oracle Database to attempt to increase or decrease the size of the datafile to the specified absolute size in bytes. There is no default, so you must specify a size.

If sufficient disk space is not available for the increased size, or if the file contains data beyond the specified decreased size, Oracle Database returns an error.

See Also: "Resizing a Datafile: Example"

END BACKUP Specify `END` `BACKUP` to take the datafile out of online backup mode. The `END` `BACKUP` clause is described more fully at the top level of the syntax of `ALTER` `DATABASE`. See "END BACKUP Clause".

alter_tempfile_clause

Use the `TEMPFILE` clause to resize your temporary datafile or specify the `autoextend_clause`, with the same effect as for a permanent datafile. The database must be open. You can identify the tempfile by name or by number. If you identify it by number, then `filenumber` is an integer representing the number found in the `FILE#` column of the `V$TEMPFILE` dynamic performance view.

Note: On some operating systems, Oracle does not allocate space for a tempfile until the tempfile blocks are actually accessed. This delay in space allocation results in faster creation and resizing of tempfiles, but it requires that sufficient disk space is available when the tempfiles are later used. To avoid potential problems, before you create or resize a tempfile, ensure that the available disk space exceeds the size of the new tempfile or the increased size of a resized tempfile. The excess space should allow for anticipated increases in disk space use by unrelated operations as well. Then proceed with the creation or resizing operation.

DROP Specify `DROP` to drop `tempfile` from the database. The tablespace remains.

If you specify `INCLUDING` `DATAFILES`, Oracle Database also deletes the associated operating system files and writes a message to the alert log for each such deleted file. You can achieve the same result using an `ALTER` `TABLESPACE` ... `DROP` `TEMPFILE` statement. Please refer to the `ALTER` `TABLESPACE` DROP Clause for more information.

autoextend_clause

Use the `autoextend_clause` to enable or disable the automatic extension of a new or existing datafile or tempfile. Please refer to file_specification for information about this clause.

logfile_clauses

The logfile clauses let you add, drop, or modify log files.

ARCHIVEL``OG

Specify `ARCHIVELOG` if you want the contents of a redo log file group to be archived before the group can be reused. This mode prepares for the possibility of media recovery. Use this clause only after shutting down your instance normally, or immediately with no errors, and then restarting it and mounting the database. Real Application Clusters must be disabled.

MANUAL Specify `MANUAL` to indicate that Oracle Database should create redo log files, but the archiving of the redo log files is controlled entirely by the user. This clause is provided for backward compatibility, for example for users who archive directly to tape. If you specify `MANUAL`, then:

- Oracle Database does not archive redo log files when a log switch occurs. You must handle this manually.

- You cannot have specified a standby database as an archivelog destinations. As a result, the database cannot be in `MAXIMUM` `PROTECTION` or `MAXIMUM` `AVAILABILITY` standby protection mode.

If you omit this clause, Oracle Database automatically archives the redo log files to the destination specified in the `LOG_ARCHIVE_DEST_``n` initialization parameters.

NOARCHIVELOG ``

Specify `NOARCHIVELOG` if you do not want the contents of a redo log file group to be archived so that the group can be reused. This mode does not prepare for recovery after media failure. Use this clause only if your instance has the database mounted but not open, and Real Application Clusters must be disabled.

[NO] FORCE LOGGING

Use this clause to put the database into or take the database out of `FORCE` `LOGGING` mode. The database must be mounted or open.

In `FORCE` `LOGGING` mode, Oracle Database logs all changes in the database except changes in temporary tablespaces and temporary segments. This setting takes precedence over and is independent of any `NOLOGGING` or `FORCE` `LOGGING` settings you specify for individual tablespaces and any `NOLOGGING` settings you specify for individual database objects.

If you specify `FORCE` `LOGGING`, Oracle Database waits for all ongoing unlogged operations to finish.

See Also: Oracle Database Administrator's Guide for information on when to use `FORCE` `LOGGING` mode

RENAME FILE Clause

This clause has the same function for logfiles that it has for datafiles and tempfiles. See "RENAME FILE Clause".

CLEAR LOGFILE Clause

Use the `CLEAR` `LOGFILE` clause to reinitialize an online redo log, optionally without archiving the redo log. `CLEAR` `LOGFILE` is similar to adding and dropping a redo log, except that the statement may be issued even if there are only two logs for the thread and may be issued for the current redo log of a closed thread.

For a standby database, if the `STANDBY_FILE_MANAGEMENT` initialization parameter is set to `AUTO`, and if any of the log files are Oracle-managed files, Oracle Database will create as many Oracle-managed log files as are in the control file. The log file members will reside in the current default log file destination.

- You must specify `UNARCHIVED` if you want to reuse a redo log that was not archived.

  Caution: Specifying `UNARCHIVED` makes backups unusable if the redo log is needed for recovery.

- You must specify `UNRECOVERABLE` `DATAFILE` if you have taken the datafile offline with the database in archivelog mode (that is, you specified `ALTER` `DATABASE` ... `DATAFILE` `OFFLINE` without the `DROP` keyword), and if the unarchived log to be cleared is needed to recover the datafile before bringing it back online. In this case, you must drop the datafile and the entire tablespace once the `CLEAR` `LOGFILE` statement completes.

  Do not use `CLEAR` `LOGFILE` to clear a log needed for media recovery. If it is necessary to clear a log containing redo after the database checkpoint, you must first perform incomplete media recovery. The current redo log of an open thread can be cleared. The current log of a closed thread can be cleared by switching logs in the closed thread.

  If the `CLEAR` `LOGFILE` statement is interrupted by a system or instance failure, then the database may hang. In this case, reissue the statement after the database is restarted. If the failure occurred because of I/O errors accessing one member of a log group, then that member can be dropped and other members added.

  See Also: "Clearing a Log File: Example"

add_logfile_clauses

Use these clauses to add redo log file groups to the database and to add new members to existing redo log file groups.

ADD LOGFILE Clause Use the `ADD` `LOGFILE` clause to add one or more redo log file groups to the specified thread or instance, making them available to the instance to which the thread is assigned.

To learn whether a logfile has been designated for online or standby database use, query the `TYPE` column of the `V$LOGFILE` dynamic performance view.

See Also: "LOGFILE Clause" of `CREATE` `DATABASE` for information on the result of this clause for Oracle-managed files if you do not specify a name for the new log file group "Adding Redo Log File Groups: Examples" Oracle Database Reference for more information on `V$LOGFILE`

INSTANCE The `INSTANCE` clause is applicable only if you are using Oracle Database with the Real Application Clusters option in parallel mode. Specify the name of the instance for which you want to add a logfile. The instance name is a string of up to 80 characters. Oracle Database automatically uses the thread that is mapped to the specified instance. If no thread is mapped to the specified instance, then Oracle Database automatically acquires an available unmapped thread and assigns it to that instance. If you specify neither this clause nor the `THREAD` clause, then Oracle Database executes the command as if you had specified the current instance. If the specified instance has no current thread mapping and there are no available unmapped threads, then Oracle Database returns an error.

GROUP The `GROUP` clause uniquely identifies the redo log file group among all groups in all threads and can range from 1 to the value specified for `MAXLOGFILES` in the `CREATE` `DATABASE` statement. You cannot add multiple redo log file groups having the same `GROUP` value. If you omit this parameter, Oracle Database generates its value automatically. You can examine the `GROUP` value for a redo log file group through the dynamic performance view `V$LOG`.

redo_log_file_spec Each `redo_log_file_spec` specifies a redo log file group containing one or more members (that is, one or more copies). If you do not specify a filename for the new log file, then Oracle Database creates Oracle-managed files according to the rules described in the "LOGFILE Clause" of `CREATE` `DATABASE`.

See Also: file_specification Oracle Database Reference for information on dynamic performance views

ADD [STANDBY] LOGFILE MEMBER Clause Use the `ADD` `LOGFILE` `MEMBER` clause to add new members to existing redo log file groups. Each new member is specified by `'filename'`. If the file already exists, it must be the same size as the other group members, and you must specify `REUSE`. If the file does not exist, Oracle Database creates a file of the correct size. You cannot add a member to a group if all of the members of the group have been lost through media failure.

You can specify `STANDBY` for symmetry, to indicate that the logfile member is for use only by a physical standby database. However, this keyword is not required. If group `integer` was added for standby database use, all of its members will be used only for standby databases as well.

You can specify an existing redo log file group in one of two ways:

GROUP integer Specify the value of the `GROUP` parameter that identifies the redo log file group.

filename(s) List all members of the redo log file group. You must fully specify each filename according to the conventions of your operating system.

See Also: "LOGFILE Clause" of `CREATE` `DATABASE` for information on the result of this clause for Oracle-managed files if you do not specify a name for the new log file group "Adding Redo Log File Group Members: Example"

drop_logfile_clauses

Use these clauses to drop redo log file groups or redo log file members.

DROP LOGFILE Clause Use the `DROP` `LOGFILE` clause to drop all members of a redo log file group. If you use this clause to drop Oracle-managed files, then Oracle Database also removes all log file members from disk. Specify a redo log file group as indicated for the `ADD` `LOGFILE` `MEMBER` clause.

- To drop the current log file group, you must first issue an `ALTER` `SYSTEM` `SWITCH` `LOGFILE` statement.

- You cannot drop a redo log file group if it needs archiving.

- You cannot drop a redo log file group if doing so would cause the redo thread to contain less than two redo log file groups.

  See Also: ALTER SYSTEM and "Dropping Log File Members: Example"

DROP LOGFILE MEMBER Clause Use the `DROP` `LOGFILE` `MEMBER` clause to drop one or more redo log file members. Each `'filename'` must fully specify a member using the conventions for filenames on your operating system.

- To drop a log file in the current log, you must first issue an `ALTER` `SYSTEM` `SWITCH` `LOGFILE` statement. Please refer to ALTER SYSTEM for more information.

- You cannot use this clause to drop all members of a redo log file group that contains valid data. To perform that operation, use the `DROP` `LOGFILE` clause.

  See Also: "Dropping Log File Members: Example"

supplemental_db_logging

Use these clauses to instruct Oracle Database to add or stop adding supplemental data into the log stream.

ADD SUPPLEMENTAL LOG Clause Specify `ADD` `SUPPLEMENTAL` `LOG` `DATA` to enable minimal supplemental logging. Specify `ADD` `SUPPLEMENTAL` `LOG` `supplemental_id_key_clause` to enable column data logging in addition to minimal supplemental logging. Oracle Database does not enable either minimal supplemental logging or supplemental logging by default.

Minimal supplemental logging ensures that LogMiner (and any products building on LogMiner technology) will have sufficient information to support chained rows and various storage arrangements such as cluster tables.

If the redo generated on one database is to be the source of changes (to be mined and applied) at another database, as is the case with logical standby, then the affected rows need to be identified using column data (as opposed to rowids). In this case, you should specify the `supplemental_id_key_clause`.

You can query the appropriate columns in the `V$DATABASE` view to determine whether any supplemental logging has already been enabled.

You can issue this statement when the database is open. However, Oracle Database will invalidate all DML cursors in the cursor cache, which will have an effect on performance until the cache is repopulated.

For a full discussion of the `supplemental_id_clause`, please refer to supplemental_id_key_clause in the documentation on `CREATE` `TABLE`.

See Also: Oracle Data Guard Concepts and Administration for information on supplemental logging on the primary database to support a logical standby database

DROP SUPPLEMENTAL LOG Clause

Use this clause to stop supplemental logging.

- Specify `DROP` `SUPPLEMENTAL` `LOG` `DATA` to instruct Oracle Database to stop placing minimal additional log information into the redo log stream whenever an update operation occurs. If Oracle Database is doing column data supplemental logging specified with the `supplemental_id_key_clause`, then you must first stop the column data supplemental logging with the `DROP` `SUPPLEMENTAL` `LOG` `supplemental_id_key_clause` and then specify this clause.

- Specify `DROP` `SUPPLEMENTAL` `LOG` `supplemental_id_key_clause` to drop some or all of the system-generated supplemental log groups. You must specify the `supplemental_id_key_clause` if the supplemental log groups you want to drop were added using that clause.

  See Also: Oracle Data Guard Concepts and Administration for information on supplemental logging

controlfile_clauses

The `controlfile_clauses` let you create or back up a control file.

CREATE STANDBY CONTROLFILE Clause

The `CREATE` `STANDBY` `CONTROLFILE` clause lets you create a control file to be used to maintain a physical or logical standby database. If the file already exists, you must specify `REUSE`.

See Also: Oracle Data Guard Concepts and Administration

BACKUP CONTROLFILE Clause

Use the `BACKUP` `CONTROLFILE` clause to back up the current control file. The database must be open or mounted when you specify this clause.

TO 'filename' Specify the file to which the control file is backed up. You must fully specify the `filename` using the conventions for your operating system. If the specified file already exists, you must specify `REUSE`.

TO TRACE Specify `TO` `TRACE` if you want Oracle Database to write SQL statements to a trace file rather than making a physical backup of the control file. You can use SQL statements written to the trace file to start up the database, re-create the control file, and recover and open the database appropriately, based on the created control file. If you issue an `ALTER` `DATABASE` `BACKUP` `CONTROLFILE` `TO` `TRACE` statement while block change tracking is enabled, the resulting script will contain a command to reenable block change tracking.

This statement issues an implicit `ALTER` `DATABASE` `REGISTER` `LOGFILE` statement, which creates incarnation records if the archived log files reside in the current archivelog destinations.

You can copy the statements from the trace file into a script file, edit the statements as necessary, and use the script if all copies of the control file are lost (or to change the size of the control file).

- Specify `AS` `filename` if you want Oracle Database to place the script into a file called `filename` rather than into the standard trace file.

- Specify `REUSE` to allow Oracle Database to overwrite any existing file called `filename`.

- `RESETLOGS` indicates that the SQL statement written to the trace file for starting the database is `ALTER` `DATABASE` `OPEN` `RESETLOGS`. This setting is valid only if the online logs are unavailable.

- `NORESETLOGS` indicates that the SQL statement written to the trace file for starting the database is `ALTER` `DATABASE` `OPEN` `NORESETLOGS`. This setting is valid only if all the online logs are available.

If you cannot predict the future state of the online logs, specify neither `RESETLOGS` nor `NORESETLOGS`. In this case, Oracle Database puts both versions of the script into the trace file, and you can choose which version is appropriate when the script becomes necessary.

standby_database_clauses

Use these clauses to activate the standby database or to specify whether it is in protected or unprotected mode.

See Also: Oracle Data Guard Concepts and Administration for descriptions of physical and logical the standby database and for information on maintaining and using standby databases

activate_standby_db_clause

Use the `ACTIVATE` `STANDBY` `DATABASE` clause to convert a standby database into a primary database.

Caution: Before using this command, please refer to Oracle Data Guard Concepts and Administration for important usage information.

PHYSICAL Specify `PHYSICAL` to activate a physical standby database. This is the default.

LOGICAL Specify `LOGICAL` to activate a logical standby database. If you have more than one logical standby database, you should first ensure that the same log data is available on all the standby systems.

FINISH APPLY This clause applies only to logical standby databases. Use it to initiate terminal apply, which is the application of any remaining redo to bring the logical standby database to the same state as the primary database. When terminal apply is complete, the database completes the switchover from logical standby to primary database.

If you require immediate restoration of the database in spite of data loss, omit this clause. The database will execute the switchover from logical standby to primary database immediately without terminal apply.

maximize_standby_db_clause

Use this clause to specify the level of protection for the data in your database environment. You specify this clause from the primary database, which must be mounted but not open.

Note: The `PROTECTED` and `UNPROTECTED` keywords have been replaced for clarity but are still supported. `PROTECTED` is equivalent to `TO` `MAXIMIZE` `PROTECTION`. `UNPROTECTED` is equivalent to `TO` `MAXIMIZE` `PERFORMANCE`.

TO MAXIMIZE PROTECTION This setting establishes maximum protection mode and offers the highest level of data protection. A transaction does not commit until all data needed to recover that transaction has been written to at least one physical standby database that is configured to use the `SYNC` log transport mode. If the primary database is unable to write the redo records to at least one such standby database, the primary database is shut down. This mode guarantees zero data loss, but it has the greatest potential impact on the performance and availability of the primary database.

TO MAXIMIZE AVAILABILITY This setting establishes maximum availability mode and offers the next highest level of data protection. A transaction does not commit until all data needed to recover that transaction has been written to at least one physical or logical standby database that is configured to use the `SYNC` log transport mode. Unlike maximum protection mode, the primary database does not shut down if it is unable to write the redo records to at least one such standby database. Instead, the protection is lowered to maximum performance mode until the fault has been corrected and the standby database has caught up with the primary database. This mode guarantees zero data loss unless the primary database fails while in maximum performance mode. Maximum availability mode provides the highest level of data protection that is possible without affecting the availability of the primary database.

TO MAXIMIZE PERFORMANCE This setting establishes maximum performance mode and is the default setting. A transaction commits before the data needed to recover that transaction has been written to a standby database. Therefore, some transactions may be lost if the primary database fails and you are unable to recover the redo records from the primary database. This mode provides the highest level of data protection that is possible without affecting the performance of the primary database.

To determine the current mode of the database, query the `PROTECTION_MODE` column of the `V$DATABASE` dynamic performance view.

See Also: Oracle Data Guard Concepts and Administration for full information on using these standby database settings

register_logfile_clause

Specify the `REGISTER` `LOGFILE` clause from the standby database to manually register log files from the failed primary. Use the `redo_log_file_spec` form of `file_specification` (see file_specification) to list regular redo log files in an operating system file system or to list Automatic Storage Management disk group redo log files.

When a log file is from an unknown incarnation, the `REGISTER` `LOGFILE` clause causes an incarnation record to be added to the `V$DATABASE_INCARNATION` view. If the newly registered log file belongs to an incarnation having a higher `RESETLOGS_TIME` than the current `RECOVERY_TARGET_INCARNATION#`, the `REGISTER` `LOGFILE` clause also causes `RECOVERY_TARGET_INCARNATION#` to be changed to correspond to the newly added incarnation record.

OR REPLACE Specify `OR` `REPLACE` to allow an existing archivelog entry in the standby database to be updated, for example, when its location or file specification changes. The system change numbers of the entries must match exactly, and the original entry must have been created by the managed standby log transmittal mechanism.

FOR logminer_session_name This clause is useful in a Streams environment. It lets you register the log file with one specified LogMiner session.

commit_switchover_clause

Use this clause to perform a switchover, in which the current primary database takes on standby status, and one standby database becomes the primary database. In a Real Application Clusters environment, all instances other than the instance from which you issue this statement must be shut down normally.

When it is not possible to perform a graceful switchover because the primary database is not available, use the `activate_standby_db_clause` instead of this clause.

PREPARE TO SWITCHOVER The `PREPARE` `TO` `SWITCHOVER` clause prepares the primary and standby databases to begin exchanging log files in preparation for the switchover.

- On the primary database, specify `PREPARE` `TO` `SWITCHOVER` `TO` `LOGICAL` `STANDBY` to enable the primary database to begin accepting log files from one of its logical standby databases.

- From the logical standby database, specify this clause to build and send its LogMiner dictionary to the primary before the switchover is committed.

COMMIT TO SWITCHOVER The `COMMIT` `TO` `SWITCHOVER` completes the switchover operation.

- On the primary database, specify `COMMIT` `TO` `SWITCHOVER` `TO` `STANDBY` to perform a database switchover of the primary database to standby database status. The primary database must be open.

- On one of the standby databases, issue a `COMMIT` `TO` `SWITCHOVER` `TO` `PRIMARY` statement to perform a switchover of this standby database to primary status. The standby database must be mounted.

- Specify `PHYSICAL` to prepare the primary database to run in the role of a physical standby database.

  - If you specify `WITH` `SESSION` `SHUTDOWN`, Oracle Database shuts down any open application sessions and rolls back uncommitted transactions as part of the execution of this statement. If you omit this clause or specify `WITHOUT` `SESSION` `SHUTDOWN` (which is the default), the statement fails if any application sessions are open.

    Restriction on `WITH SESSION SHUTDOWN`: This clause is not necessary or supported for a logical database.

  - Specify `WAIT` if you want Oracle Database to return control after the completion of the `SWITCHOVER` command. Specify `NOWAIT` if you want Oracle Database to return control before the switchover operation is complete. The default is `WAIT`.

- Specify `LOGICAL` to prepare the primary database to run in the role of a logical standby database. If you specify `LOGICAL`, you must then issue an `ALTER` `DATABASE` `START` `LOGICAL` `STANDBY` `APPLY` statement.

CANCEL Specify `CANCEL` to cancel the switchover from primary to standby database. This clause is necessary to stop the shipping of log files from a logical standby database to the primary database.

See Also: Oracle Data Guard Concepts and Administration for full information on switchover between primary and standby databases

start_standby_clause

Specify the `START` `LOGICAL` `STANDBY` `APPLY` clause to begin applying redo logs to a logical standby database.

- Specify `IMMEDIATE` to apply redo data from the current standby redo log file.

- Specify `NODELAY` if you want Oracle Database to ignore a delay for this apply. This is useful if the primary database is no longer present, which would otherwise require a PL/SQL call to be made.

- Specify `INITIAL` the first time you apply the logs to the standby database.

- Specify `NEW` `PRIMARY` on a logical standby not participating in a role transition, after a switchover or failover operation has resulted in a new primary database.

- Specify `SKIP` `FAILED` [`TRANSACTION`] to skip the last transaction in the events table and restart the apply.

- Specify `FINISH` to force the standby redo logfile information into archived logs. If the primary database becomes disabled, you can then apply the data in the redo log files.

stop_standby_clause

Use this clause to stop the log apply services. This clause applies only to logical standby databases, not to physical standby databases. Use the `STOP` clause to stop the apply in an orderly fashion.

convert_standby_clause

Use this clause to convert a primary database outside of the Data Guard environment into a physical standby database. This operation enables the database to join the Data Guard environment. You must shut down and restart the database after this `ALTER` `DATABASE` statement is issued.

See Also: Oracle Data Guard Concepts and Administration for more information on including a database in a Data Guard environment

default_settings_clauses

Use these clauses to modify the default settings of the database.

CHARACTER SET, NATIONAL CHARACTER SET

You can no longer change the database character set or the national character set using the `ALTER` `DATABASE` statement. Please refer to Oracle Database Globalization Support Guide for information on database character set migration.

SET DEFAULT TABLESPACE Clause

Use this clause to specify or change the default type of subsequently created tablespaces. Specify `BIGFILE` or `SMALLFILE` to indicate whether the tablespaces should be bigfile or smallfile tablespaces.

- A bigfile tablespace contains only one datafile or tempfile, which can contain up to approximately 4 billion (232) blocks. The maximum size of the single datafile or tempfile is 128 terabytes (TB) for a tablespace with 32K blocks and 32TB for a tablespace with 8K blocks.

- A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to approximately 4 million (222) blocks.

See Also: Oracle Database Administrator's Guide for more information about bigfile tablespaces "Setting the Default Type of Tablespaces: Example"

DEFAULT TABLESPACE Clause

Specify this clause to establish or change the default permanent tablespace of the database. The tablespace you specify must already have been created. After this operation completes, Oracle Database automatically reassigns to the new default tablespace all non-`SYSTEM` users. All objects subsequently created by those users will by default be stored in the new default tablespace. If you are replacing a previously specified default tablespace, you can move the previously created objects from the old to the new default tablespace, and then drop the old default tablespace if you want to.

DEFAULT TEMPORARY TABLESPACE Clause

Specify this clause to change the default temporary tablespace of the database to a new tablespace or tablespace group.

- Specify `tablespace` to indicate the new default temporary tablespace of the database. After this operation completes, Oracle Database automatically reassigns to the new default temporary tablespace all users who had been assigned to the old default temporary tablespace. You can then drop the old default temporary tablespace if you want to.

- Specify `tablespace_group_name` to indicate that all tablespaces in the tablespace group specified by `tablespace_group_name` are now default temporary tablespace for the database. After this operation completes, users who have not been explicitly assigned a default temporary tablespace can create temporary segments in any of the tablespaces that are part of `tablespace_group_name`. You cannot drop the old default temporary tablespace if it is part of the default temporary tablespace group.

To learn the name of the current default temporary tablespace or default temporary tablespace group, query the `TEMPORARY_TABLESPACE` column of the `ALL_`, `DBA-`, or `USER_USERS` data dictionary views.

Restrictions on Default Temporary Tablespaces Default temporary tablespaces are subject to the following restrictions:

- The tablespace you assign or reassign as the default temporary tablespace must have a standard block size.

- If the `SYSTEM` tablespace is locally managed, the tablespace you specify as the default temporary tablespace must also be locally managed.

See Also: Oracle Database Administrator's Guide for information on tablespace groups "Changing the Default Temporary Tablespace: Examples"

instance_clauses

In an Oracle Real Application Clusters environment, specify `ENABLE` `INSTANCE` to enable the thread that is mapped to the specified database instance. The thread must have at least two redo log file groups, and the database must be open.

Specify `DISABLE` `INSTANCE` to disable the thread that is mapped to the specified database instance. The name of the instance is a string of up to 80 characters. If no thread is currently mapped to the specified instance, then Oracle Database returns an error. The database must be open, but you cannot disable a thread if an instance using it has the database mounted.

In earlier releases, you could enable or disable a thread. This clause has been deprecated, because the `ENABLE` | `DISABLE` `INSTANCE` clause achieves the same purpose and is easier to use. The `ENABLE` | `DISABLE` `THREAD` clause is supported for backward compatibility only.

See Also: Oracle Real Application Clusters Administrator's Guide for more information on enabling and disabling instances

RENAME GLOBAL_NAME Clause

Specify `RENAME` `GLOBAL_NAME` to change the global name of the database. The `database` is the new database name and can be as long as eight bytes. The optional `domain` specifies where the database is effectively located in the network hierarchy. The database must be open.

Note: Renaming your database does not change global references to your database from existing database links, synonyms, and stored procedures and functions on remote databases. Changing such references is the responsibility of the administrator of the remote databases.

See Also: Oracle Database Heterogeneous Connectivity Administrator's Guide for more information on global names and "Changing the Global Database Name: Example"

BLOCK CHANGE TRACKING Clauses

The block change tracking feature causes Oracle Database to keep track of the physical locations of all database updates. This information is maintained in a separate file called the block change tracking file. If you are using Oracle-managed files, Oracle Database automatically creates the block change tracking file in the location specified by `DB_CREATE_FILE_DEST`. If you are not using Oracle-managed files, you must specify the change tracking filename. Oracle Database uses change tracking data for some internal tasks, such as increasing the performance of incremental backups. You can enable or disable block change tracking with the database either open or mounted, in either archivelog or noarchivelog mode.

ENABLE BLOCK CHANGE TRACKING This clause enables block change tracking and causes Oracle Database to create a block change tracking file.

- Specify `USING` `FILE` '`filename`' if you want to name the block change tracking file instead of letting Oracle Database generate a name for it. You must specify this clause if you are not using Oracle-managed files.

- Specify `REUSE` to allow Oracle Database to overwrite an existing block change tracking file of the same name.

DISABLE BLOCK CHANGE TRACKING Specify this clause if you want Oracle Database to stop tracking changes and delete the existing block change tracking file.

See Also: Oracle Database Backup and Recovery Basics for information on setting up block change tracking and "Enabling and Disabling Block Change Tracking: Examples"

flashback_mode_clause

Use this clause to put the database in or take the database out of `FLASHBACK` mode. You can specify this clause only if the database is in archivelog mode and you have already prepared a flash recovery area for the database.You can specify this clause when the database is mounted but not open.

See Also: Oracle Database Backup and Recovery Basics for information on preparing the flash recovery area for Flashback operations

FLASHBACK ON Use this clause to put the database in `FLASHBACK` mode. When the database is in `FLASHBACK` mode, Oracle Database automatically creates and manages Flashback Database logs in the flash recovery area. Users with `SYSDBA` system privilege can then issue a `FLASHBACK` `DATABASE` statement.

FLASHBACK OFF Use this clause to take the database out of `FLASHBACK` mode. Oracle Database stops logging Flashback data and deletes all existing Flashback Database logs. Any attempt to issue a `FLASHBACK` `DATABASE` will fail with an error.

set_time_zone_clause

This clause has the same semantics in `CREATE` `DATABASE` and `ALTER` `DATABASE` statements. When used in with `ALTER` `DATABASE`, this clause resets the time zone of the database. To determine the time zone of the database, query the built-in function DBTIMEZONE. After setting or changing the time zone with this clause, you must restart the database for the new time zone to take effect.

Oracle Database normalizes all new `TIMESTAMP` `WITH` `LOCAL` `TIME` `ZONE` data to the time zone of the database when the data is stored on disk.Oracle Database does not automatically update existing data in the database to the new time zone. Therefore, you cannot reset the database time zone if there is any `TIMESTAMP` `WITH` `LOCAL` `TIME` `ZONE` data in the database. You must first delete or export the `TIMESTAMP` `WITH` `LOCAL` `TIME` `ZONE` data and then reset the database time zone. For this reason, Oracle does not encourage you to change the time zone of a database that contains data.

For a full description of this clause, please refer to set_time_zone_clause in the documentation on `CREATE` `DATABASE`.

security_clause

Use the `security_clause` (`GUARD`) to protect data in the database from being changed. You can override this setting for a current session using the `ALTER` `SESSION` `DISABLE` `GUARD` statement. Please refer to ALTER SESSION for more information.

ALL Specify `ALL` to prevent all users other than `SYS` from making any changes to the database.

STANDBY Specify `STANDBY` to prevent all users other than `SYS` from making changes to any database object being maintained by logical standby. This setting is useful if you want report operations to be able to modify data as long as it is not being replicated by logical standby.

See Also: Oracle Data Guard Concepts and Administration for information on logical standby

NONE Specify `NONE` if you want normal security for all data in the database.

Caution: Oracle strongly recommends that you not use this setting on a logical standby database.

Examples

READ ONLY / READ WRITE: Example The following statement opens the database in read-only mode:

    ALTER DATABASE OPEN READ ONLY;

The following statement opens the database in read/write mode and clears the online redo logs:

    ALTER DATABASE OPEN READ WRITE RESETLOGS;

Using Parallel Recovery Processes: Example The following statement performs tablespace recovery using parallel recovery processes:

    ALTER DATABASE
       RECOVER TABLESPACE tbs_03
       PARALLEL;

Adding Redo Log File Groups: Examples The following statement adds a redo log file group with two members and identifies it with a `GROUP` parameter value of 3:

    ALTER DATABASE
      ADD LOGFILE GROUP 3
        ('diska:log3.log' ,
         'diskb:log3.log') SIZE 50K;

The following statement adds a redo log file group containing two members to thread 5 (in a Real Application Clusters environment) and assigns it a `GROUP` parameter value of 4:

    ALTER DATABASE
        ADD LOGFILE THREAD 5 GROUP 4
            ('diska:log4.log',
             'diskb:log4:log');

Adding Redo Log File Group Members: Example The following statement adds a member to the redo log file group added in the previous example:

    ALTER DATABASE
       ADD LOGFILE MEMBER 'diskc:log3.log'
       TO GROUP 3;

Dropping Log File Members: Example The following statement drops one redo log file member added in the previous example:

    ALTER DATABASE
        DROP LOGFILE MEMBER 'diskb:log3.log';

The following statement drops all members of the redo log file group 3:

    ALTER DATABASE DROP LOGFILE GROUP 3;

Renaming a Log File Member: Example The following statement renames a redo log file member:

    ALTER DATABASE
        RENAME FILE 'diskc:log3.log' TO 'diskb:log3.log';

The preceding statement only changes the member of the redo log group from one file to another. The statement does not actually change the name of the file `diskc:log3.log` to `diskb:log3.log`. Before issuing this statement, you must change the name of the file through your operating system.

Setting the Default Type of Tablespaces: Example The following statement specifies that subsequently created tablespaces be created as bigfile tablespaces by default:

    ALTER DATABASE
        SET DEFAULT BIGFILE TABLESPACE;

Changing the Default Temporary Tablespace: Examples The following statement makes the `tbs_5` tablespace (created in "Creating a Temporary Tablespace: Example") the default temporary tablespace of the database. This statement either establishes a default temporary tablespace if none was specified at create time, or replaces an existing default temporary tablespace with `tbs_05`:

    ALTER DATABASE
       DEFAULT TEMPORARY TABLESPACE tbs_05;

Alternatively, a group of tablespaces can be defined as the default temporary tablespace by using a tablespace group. The following statement makes the tablespaces in the tablespace group `tbs_group_01` (created in "Adding a Temporary Tablespace to a Tablespace Group: Example") the default temporary tablespaces of the database:

    ALTER DATABASE
       DEFAULT TEMPORARY TABLESPACE tbs_grp_01;

Creating a New Datafile: Example The following statement creates a new datafile `tbs_f04.dbf` based on the file `tbs_f03.dbf`. Before creating the new datafile, you must take the existing datafile (or the tablespace in which it resides) offline.

    ALTER DATABASE
        CREATE DATAFILE 'tbs_f03.dbf'
                     AS 'tbs_f04.dbf';

Manipulating Tempfiles: Example The following takes offline the tempfile temp02.dbf created in Adding and Dropping Datafiles and Tempfiles: Examples and then renames the tempfile:

    ALTER DATABASE TEMPFILE 'temp02.dbf' OFFLINE;

    ALTER DATABASE RENAME FILE 'temp02.dbf' TO 'temp03.dbf';

The statement renaming the tempfile requires that you first create the file `temp03.dbf` on the operating system.

Changing the Global Database Name: Example The following statement changes the global name of the database and includes both the database name and domain:

    ALTER DATABASE
        RENAME GLOBAL_NAME TO demo.world.oracle.com;

Enabling and Disabling Block Change Tracking: Examples The following statement enables block change tracking and causes Oracle Database to create a block change tracking file named `tracking_file` and overwrite the file if it already exists:

    ALTER DATABASE
      ENABLE BLOCK CHANGE TRACKING
        USING FILE 'tracking_file' REUSE;

The following statement disables block change tracking and deletes the existing block change tracking file:

    ALTER DATABASE
      DISABLE BLOCK CHANGE TRACKING;

Resizing a Datafile: Example The following statement attempts to change the size of datafile `diskb:tbs_f5.dat`:

    ALTER DATABASE
        DATAFILE 'diskb:tbs_f5.dat' RESIZE 10 M;

Clearing a Log File: Example The following statement clears a log file:

    ALTER DATABASE
        CLEAR LOGFILE 'diskc:log3.log';

Database Recovery: Examples The following statement performs complete recovery of the entire database, letting Oracle Database generate the name of the next archived redo log file needed:

    ALTER DATABASE
      RECOVER AUTOMATIC DATABASE;

The following statement explicitly names a redo log file for Oracle Database to apply:

    ALTER DATABASE
        RECOVER LOGFILE 'diskc:log3.log';

The following statement recovers the standby datafile `/finance/stbs_21.f`, using the corresponding datafile in the original standby database, plus all relevant archived logs and the current standby database control file:

    ALTER DATABASE
       RECOVER STANDBY DATAFILE '/finance/stbs_21.f'
       UNTIL CONTROLFILE;

The following statement performs time-based recovery of the database:

    ALTER DATABASE
        RECOVER AUTOMATIC UNTIL TIME '2001-10-27:14:00:00';

Oracle Database recovers the database until 2:00 p.m. on October 27, 2001.

For an example of recovering a tablespace, see "Using Parallel Recovery Processes: Example".

Recovering a Managed Standby Database: Examples The following statement recovers the standby database in managed standby recovery mode:

    ALTER DATABASE
       RECOVER MANAGED STANDBY DATABASE;

The following statement puts the database in managed standby recovery mode. The managed recovery process will wait up to 60 minutes for the next archive log:

    ALTER DATABASE
       RECOVER MANAGED STANDBY DATABASE TIMEOUT 60;

If each subsequent log arrives within 60 minutes of the last log, recovery continues indefinitely or until manually terminated.

The following statement terminates the managed recovery operation:

    ALTER DATABASE
       RECOVER MANAGED STANDBY DATABASE CANCEL IMMEDIATE;

The managed recovery operation terminates before the next group of redo is read from the current redo log file. Media recovery ends during the process of applying redo from the current redo log file.